<?php
/**
 * 查询构造器
 * 
 * 参考：
 * - http://www.hibernate.org/hib_docs/v3/api/org/hibernate/Criteria.html
 * - http://www.hibernate.org/hib_docs/v3/api/org/hibernate/criterion/Restrictions.html
 * - http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
 *
 */
class IDbQuery extends IObject {
	/**
	 * Enter description here...
	 *
	 * @var IDAO
	 */
	private $dao;
	private $pks = array();
	private $orders = array();
	private $attrs = array();
	private $conds = array();
	private $size;
	private $offset;
	private $joins = array();
	/**
	 * Enter description here...
	 *
	 * @var IDbDriver
	 */
	private $driver;
	private $resultAttrs = array();
	private $groups = array();
	private $params = array();
	private $havings = array();
	
	private $tblAlias = array();
	
	private $type;
	
	private $seperator = "____";
	
	/**
	 * Enter description here...
	 *
	 * array(
	 *    "logs" => array("fk" => "category_id", "pk" => "id", "target" => "Category")
	 * )
	 * 
	 * @var unknown_type
	 */
	private $hasManys = array();
	
	private $folder = true;
	
	private $queryType;
	
	/**
	 * Enter description here...
	 *
	 * @param unknown_type $record
	 */
	public function __construct($record) {
		$this->dao = IDAO::dao($record);
		$this->driver = $this->dao->driver();
		$this->type = $this->dao->type();
	}
	
	/**
	 * Enter description here...
	 *
	 * @param array|scalar $pkValue
	 * @return IDbQuery
	 */
	public function pk($pkValue) {
		$args = func_get_args();
		foreach ($args as $arg) {
			if (is_array($arg)) {
				$this->pks = arrayJoin($this->pks, $arg);
			}
			else {
				$this->pks[] = $arg;
			}
		}
		return $this;
	}
	
	/**
	 * Enter description here...
	 *
	 * @param unknown_type $order
	 * @param unknown_type $type ASC/DESC
	 * @return IDbQuery
	 */
	public function order($order, $type = null) {
		if (is_array($order)) {
			$this->orders = arrayJoin($this->orders, $order);
		}
		else {
			$this->orders[] = array($order, $type);
		}
		return $this;
	}
	
	/**
	 * Enter description here...
	 *
	 * @param unknown_type $attr
	 * @return IDbQuery
	 */
	public function asc($attr) {
		$this->order($attr, "ASC");
		return $this;
	}
	
	/**
	 * Enter description here...
	 *
	 * @param unknown_type $attr
	 * @return IDbQuery
	 */
	public function desc($attr) {
		$this->order($attr, "DESC");
		return $this;
	}
	
	/**
	 * Enter description here...
	 *
	 * @param array $attrs
	 * @return IDbQuery
	 */
	private function attrs(array $attrs) {
		$this->attrs = array_merge($this->attrs, $attrs);
		return $this;
	}
	
	/**
	 * Enter description here...
	 *
	 * @param unknown_type $name
	 * @param unknown_type $value
	 * @return IDbQuery
	 */
	public function attr($name, $value = null) {
		if (is_array($name)) {
			$this->attrs($name);
		}
		else {
			$this->attrs[$name] = $value;
		}
		return $this;
	}
	
	/**
	 * Enter description here...
	 *
	 * @param array|string $cond
	 * @return IDbQuery
	 */
	public function cond($cond) {
		if (is_array($cond)) {
			$this->conds = arrayJoin($this->conds, $cond, true);
		}
		else {
			$this->conds[] = $cond;
		}
		return $this;
	}
	
	/**
	 * Enter description here...
	 *
	 * @param unknown_type $size
	 * @return IDbQuery
	 */
	public function size($size) {
		$size = intval($size);
		if ($size <= 0) {
			throw new IActiveRecordException("size must great than 0");
		}
		$this->size = $size;
		return $this;
	}
	
	/**
	 * Enter description here...
	 *
	 * @param unknown_type $offset
	 * @return IDbQuery
	 */
	public function offset($offset) {
		$offset = intval($offset);
		if ($offset < 0) {
			throw new IActiveRecordException("offset cannot lower than 0");
		}
		$this->offset = $offset;
		return $this;
	}
	
	/**
	 * Enter description here...
	 * 
	 * array(
	 *    "user",
	 *    array("book", "LEFT"),
	 * );
	 *
	 * @param array $joins
	 * @return IDbQuery
	 */
	private function joins(array $joins) {
		foreach ($joins as $join) {
			if (is_string($join)) {
				$this->joins[$join] = array($join, "LEFT");
			}
			else if (is_array($join) && !empty($join)) {
				$this->joins[$join[0]] = $join;
			}
		}
		return $this;
	}
	
	/**
	 * Enter description here...
	 *
	 * @param unknown_type $attr
	 * @param unknown_type $type LEFT/RIGHT/INNER
	 * @return IDbQuery
	 */
	public function join($attr, $type = "LEFT") {
		$type = strtoupper($type);
		if (is_array($attr)) {
			$this->joins($attr);
		}
		else {
			if (!in_array($type, array("LEFT", "RIGHT", "INNER"))) {
				throw new IActiveRecordException("unknown join type '{$type}', only 'left,right,inner' are supported currently");
			}
			$this->joins[$attr] = array($attr, $type);
		}
		return $this;
	}
	
	private function groups(array $groups) {
		$this->groups = $groups;
		return $this;
	}
	
	/**
	 * Enter description here...
	 *
	 * @param unknown_type $attr
	 * @param unknown_type $order
	 * @return IDbQuery
	 */
	public function group($attr, $order = null) {
		if (is_array($attr)) {
			$this->groups($attr);
		}
		else {
			$this->groups[] = array($attr, $order);
		}
		return $this;
	}
	
	/**
	 * Enter description here...
	 *
	 * @param unknown_type $cond
	 * @return IDbQuery
	 */
	public function having($cond) {
		if (is_array($cond)) {
			$this->havings = arrayJoin($this->havings, $cond, true);
		}
		else {
			$this->havings[] = $cond;
		}
		return $this;
	}
	
	/**
	 * Enter description here...
	 *
	 * @param unknown_type $attr1
	 * @param unknown_type $attr2
	 * @return IDbQuery
	 */
	public function result($attr1 = null, $attr2 = null) {
		$args = func_get_args();
		$hasManys = array();
		foreach ($args as $arg) {
			$arg = trim($arg);
			if (is_string($arg) && $arg != "") {
				$attrs = explode(",", $arg);
			}
			else if (is_array($arg)) {
				$attrs = $arg;
			}
			foreach ($attrs as $attr) {
				$attr = trim($attr);
				if ($attr != "") {
					if (!preg_match("/^([a-z0-9_]+)(\s*\.\s*([a-z0-9_]+|\*))?$/i", $attr)) {
						$this->resultAttrs[] = $attr;
					}
					else if (!strstr($attr, ".")) {//单个字段
						$this->resultAttrs[] = "self." . ($attr == "*"?"*":$attr);
					}
					else {//有关联
						list($_alias, $_attr) = explode(".", $attr);
						if ($_alias == "self") {
							$this->resultAttrs[] = $attr;
						}
						else {
							$relation = $this->dao->relation($_alias);
							if (empty($relation)) {
								throw new IActiveRecordException("no relation for '{$_alias}'");
							}
							//给每个字段加一个别名
							if ($_attr != "*") {
								$this->resultAttrs[] = $attr . " AS " .  $this->driver->quoteKeyword($_alias . $this->seperator . $_attr);
								if ($relation["type"] == IDbRecord::HAS_MANY) {
									if(!isset($this->hasManys[$_alias])) {
										$this->hasManys[$_alias] = array();
									}
									$this->hasManys[$_alias][] = $attr;
								}
							}
							else {
								$dao = IDAO::dao($relation["target"]);
								foreach ($dao->columns() as $column) {
									$this->resultAttrs[] = $_alias . "." . $column . " AS " . $this->driver->quoteKeyword($_alias . $this->seperator . $column);
									if ($relation["type"] == IDbRecord::HAS_MANY) {
										if(!isset($this->hasManys[$_alias])) {
											$this->hasManys[$_alias] = array();
										}
										$this->hasManys[$_alias][] = $_alias . "." . $column;					
									}
								}
							}
						}
					}
				}
			}
		}

		return $this;
	}
	
	private function params(array $params) {
		$this->params = array_merge($this->params, $params);
		return $this;
	}
	
	/**
	 * Enter description here...
	 *
	 * @param unknown_type $name
	 * @param unknown_type $value
	 * @return IDbQuery
	 */
	public function param($name, $value) {
		if (is_array($name)) {
			$this->params($name);
		}
		else {
			$this->params[$name] = $value;
		}
		return $this;
	}
	
	/**
	 * Enter description here...
	 *
	 * @param unknown_type $folder
	 * @return IDbQuery
	 */
	public function folder($folder = true) {
		$this->folder = $folder;
		return $this;
	}
	
	/**
	 * 转换成SQL字符串
	 *
	 * @return string
	 * @since 1.0
	 */
	public function asSql() {
		//当前的ID一定要取出来
		if ($this->queryType != "count") {
			if (!empty($this->resultAttrs)) {
				$this->result("self." . $this->dao->pkName());
			}
			
			//处理hasMany
			if ($this->folder && !empty($this->hasManys)) {
				foreach ($this->hasManys as $alias=>$fkColumns) {
					$relation = $this->dao->relation($alias);
					$dao = IDAO::dao($relation["target"]);
					$pk = $dao->pkName();
					if (!in_array($alias . "." . $pk, $fkColumns)) {
						$this->result($alias . "." . $pk);
					}
				}
			}
		}
		
		$sql = "SELECT \n";
		
		//resultAttrs 
		// * | id,name,pass,created_time | id,name,pass,profile | id,profile.id,profile.birthday
		if (empty($this->resultAttrs)) {
			$this->resultAttrs[] = "self.*";
		}
		$sql .= implode(",", array_unique($this->resultAttrs)) . " \n";
		$sql .= "FROM " . $this->driver->quoteKeyword($this->dao->table()) . " AS " . $this->_alias($this->type) . " \n";
		
		//join
		$joinHolder = "<join></join>";
		$sql .= $joinHolder;
		
		//pks
		if (!empty($this->pks)) {
			$pks = array();
			$key = 0;
			foreach ($this->pks as $pk) {
				$this->pks[$key] = $this->driver->quote($pk);
				$key ++;
			}
			if (count($this->pks) == 1) {
				$this->conds[] = "self." . $this->dao->pkName() . "=" . $this->pks[0] . "\n";
			}
			else {
				$this->conds[] = "self." . $this->dao->pkName() . " IN (" . implode(",", $this->pks) . ")";
			}
		}
		
		//attr
		foreach ($this->attrs as $name=>$value) {
			if (is_array($value)) {
				$values = array();
				foreach ($value as $_value) {
					$values[] = $this->driver->quote($_value);
				}
				if (!empty($values)) {
					$this->conds[] = "self.{$name} IN (" . implode(",", $values) . ")";
				}
			}
			else {
				$this->conds[] = "self.{$name}=" . $this->driver->quote($value);
			}
		}
		
		//where
		if (!empty($this->conds)) {
			if (count($this->conds) > 1) {
				$sql .= "WHERE (" . implode(") AND (", $this->conds) . ")" . " \n";
			}
			else {
				$sql .= "WHERE " . $this->conds[0] . " \n";
			}
		}
		
		//group
		if (!empty($this->groups)) {
			$groups = array();
			foreach ($this->groups as $group) {
				$groups[] = implode(" ", $group);
			}
			$sql .= "GROUP BY " . implode(",", $groups) . " \n";
		}
		
		//having
		if (!empty($this->havings)) {
			$sql .= "HAVING (" . implode(") AND (", $this->havings) . ") \n";
		} 
		
		//order
		if (!empty($this->orders)) {
			$orders = array();
			foreach ($this->orders as $order) {
				if (is_array($order)) {
					$orders[] = implode(" ", $order);
				}
				else {
					$orders[] = $order;
				}
			}
			$sql .= "ORDER BY " . implode(",", $orders) . " \n";
		}
		
		//offset|size
		if ($this->offset >= 0 && $this->size > 0) {
			$sql = $this->driver->limit($sql, $this->size, $this->offset);
		}
		
		//sql
		$sql = preg_replace_callback("/\b([a-z0-9_]+)\s*\.\s*([a-z0-9_]+\b|\*)/i", array($this, "_sqlAttrCallback"), $sql);
		
		//join
		$joinSql = array();
		foreach ($this->joins as $join) {
			if (is_array($join) && isset($join[0]) && isset($join[1])) {
				$attr = $join[0];
				$type = $join[1];
				$relation = $this->dao->relation($attr);
				if ($relation != null) {
					$relationDAO = IDAO::dao($relation["target"]);
					$joinSql[] = $type . " JOIN " . $this->driver->quoteKeyword($relationDAO->table()) . " AS " . $this->_alias($relation["target"]) .   " ON self." . $relation["fk"] . "=" . $attr . "." . $relation["by"];
				}
				//else throw exception
			}
		}
		if (!empty($joinSql)) {
			$sql = str_replace($joinHolder, implode(" \n", $joinSql) . " \n", $sql);
		}
		else {
			$sql = str_replace($joinHolder, "", $sql);
		}
		
		$sql = preg_replace_callback("/\b([a-z0-9_]+)\s*\.\s*([a-z0-9_]+\b|\*)/i", array($this, "_sqlAttrCallback"), $sql);
		return $sql;	
	}
	
	/**
	 * 转换为字符串
	 *
	 * @return string
	 * @since 1.0
	 */
	public function __toString() {
		return $this->asSql();
	}
	
	private function findAllAsArray() {
		$statment = $this->driver->prepare($this->asSql(), $this->params);
		$statment->execute();
		$result = $statment->fetchAll(IDb::FETCH_ASSOC);
		if ($this->queryType == "count") {
			return $result;
		}
		$ret = array();
		/**
		 * array(
		 *    1 => array(
		 *       "logs" => array(
		 * 			array("id" => "title" => "xx"),
		 *			array("id2" => "title" => "xx2"),
		 *          ...
		 * 		  ),
		 *		  "books" => array(
		 *            ...
		 *        )
		 *    )
		 *
		 * )
		 */
		$hasManys = array();
		foreach ($result as $index=>$row) {
			$retRow = array();
			$pkValue = $row[$this->dao->pkName()];
			foreach ($row as $name=>$val) {
				if (strstr($name, $this->seperator)) {
					list($alias, $column) = explode($this->seperator, $name);
					$relation = $this->dao->relation($alias);
					if ($relation["type"] == IDbRecord::BELONGS_TO || $relation["type"] == IDbRecord::HAS_ONE || !$this->folder) {
						if (!isset($retRow[$alias])) {
							$retRow[$alias] = array();
						}
						$retRow[$alias][$column] = $val;
					}
					else {
						$dao = IDAO::dao($relation["target"]);
						$aliasPkValue = $row[$alias . $this->seperator . $dao->pkName()];
						if (!isset($hasManys[$pkValue])) {
							$hasManys[$pkValue] = array();
						}
						if (!isset($hasManys[$pkValue][$alias])) {
							$hasManys[$pkValue][$alias] = array();
						}
						if (!isset($hasManys[$pkValue][$alias][$aliasPkValue])) {
							$hasManys[$pkValue][$alias][$aliasPkValue] = array();
						}
						$hasManys[$pkValue][$alias][$aliasPkValue][$column] = $val;
					}
				}
				else {
					$retRow[$name] = $val;
				}
			}
			if (!$this->folder) {
				$ret[] = $retRow;
			}
			else if (!isset($ret[$pkValue])) {
				$ret[$pkValue] = $retRow;
			}
		}
		
		foreach ($hasManys as $pk => $attrs) {
			foreach ($attrs as $alias=>$rows) {
				$rows = array_filter($rows, array($this, "removeEmptyArrayCallback"));
				$ret[$pk][$alias] = $rows;
			}
		}
		
		return array_values($ret);
	}
	
	/**
	 * Enter description here...
	 *
	 * @param unknown_type $array
	 * @return array|IDbRecord
	 */
	public function find($pk = null, $array = false) {
		$this->size(1);
		$all = array();
		if ($pk != null) {
			$all = $this->pk($pk)->findAll($array);
		}
		else {
			$all = $this->findAll($array);
		}
		if (!empty($all)) {
			return $array?$all[0]:$all->record(0);
		}
		return $array?array():null;
	}
	
	public function findAll($array = true) {
		$result = $this->findAllAsArray();
		if ($array) {//返回数组
			$this->clear();
			return $result;
		}
		$set = new IDbRecordSet($this->dao);
		$set->setRecords($result);
		$this->clear();
		return $set;
	}
	
	private function _alias($class) {
		if (!isset($this->tblAlias[$class])) {
			$this->tblAlias[$class] =  uniqid($class . "_");
		}
		return $this->driver->quoteKeyword($this->tblAlias[$class]);
	}
	
	private function _sqlAttrCallback($match) {
		$target = $match[1];
		$attr = $match[2];
		if ($target == "self") {
			return $this->_alias($this->type) . "." . ($attr == "*"?"*":$this->driver->quoteKeyword($attr));
		}
		$relation = $this->dao->relation($target);
		if ($relation == null) {
			return $match[0];
		}
		if (!isset($this->joins[$target])) {
			$this->join($target);
		}
		return $this->_alias($relation["target"]) . "." . ($attr == "*"?"*":$this->driver->quoteKeyword($attr));
	}
	
	public function clear() {
		$this->pks = array();
		$this->orders = array();
		$this->attrs = array();
		$this->conds = array();
		$this->size = null;
		$this->offset = null;
		$this->joins = array();
		$this->resultAttrs = array();
		$this->groups = array();
		$this->params = array();
		$this->havings = array();
		$this->tblAlias = array();
		$this->hasManys = array();
		$this->folder = true;
	}
	
	private function isEmptyArray($array) {
		if (empty($array)) {
			return true;
		}
		$empty = true;
		foreach ($array as $value) {
			if (!is_null($value)) {
				$empty = false;
				break;
			}
		}
		return $empty;
	}
	
	private function removeEmptyArrayCallback($array) {
		if ($this->isEmptyArray($array)) {
			return false;
		}
		return $array;
	}
	
	public function driver() {
		return $this->driver;
	}
	
	public function __call($method, $params) {
		if (preg_match("/^findBy(\w+)$/", $method, $match) && isset($params[0])) {
			$retArray = isset($params[1])?$params[1]:false;
			$column = IVM::singleton()->nameToC($match[1]);
			return $this->attr($column, $params[0])->find(null, $retArray);
		}
		if (preg_match("/^findAllBy(\w+)$/", $method, $match) && isset($params[0])) {
			$retArray = isset($params[1])?$params[1]:false;
			$column = IVM::singleton()->nameToC($match[1]);
			return $this->attr($column, $params[0])->findAll($retArray);
		}
		
		throw new IException("call to undefined method " . __CLASS__ . "::{$method}()");
	}
	
	private function queryType($queryType) {
		$this->queryType = strtolower($queryType);
		return $this;
	}
	
	public function count($attr = null) {
		$this->resultAttrs = array();
		$uniqueKey = uniqid("count");
		$attr = $attr == null?"*":$attr;
		$array = $this->queryType("count")->result("COUNT({$attr}) AS {$uniqueKey}")->find(null, true);
		return !empty($array)?$array[$uniqueKey]:0;		
	}
	
	/**
	 * Enter description here...
	 *
	 * @param IPager $pager
	 * @param unknown_type $array
	 * @return IPager
	 */
	public function queryPage(IPager $pager, $array = true) {
		$query = clone $this;
		$pager->setTotal($query->count());
		$pager->setRows($this->offset($pager->offset())->size($pager->size())->findAll($array));
		return $pager;
	}
}
	
?>